package cn.edu.buaa.treehole.dao.implement;

import cn.edu.buaa.treehole.dao.ReplyDao;
import cn.edu.buaa.treehole.dao.exception.DaoException;
import cn.edu.buaa.treehole.dao.exception.RidHasExistException;
import cn.edu.buaa.treehole.dao.exception.RidNotExistException;
import cn.edu.buaa.treehole.dao.util.DbAccess;
import cn.edu.buaa.treehole.dao.util.DbConnection;
import cn.edu.buaa.treehole.pojo.ReplyInfo;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;

/*
 * @author: Zhu Leyan
 */
@Repository
public class ReplyDaoImpl implements ReplyDao {

    private final DbConnection conn;

    private static final String insert = "INSERT INTO reply (pid, rid, content, signature, date) " +
            "VALUES (%d, %d, \"%s\", \"%s\", \"%s\")";

    private static final String selectPid = "SELECT * FROM reply WHERE pid=%d";

    private static final String selectPidAndRid = "SELECT * FROM reply WHERE pid=%d AND rid=%d";

    private static final String deletePidAndRid = "DELETE FROM reply WHERE pid=%d AND rid=%d";

    public ReplyDaoImpl() {
        this.conn = DbConnection.getInstance(DbAccess.url, DbAccess.usr, DbAccess.pwd);
    }

    private Statement createStatement() throws SQLException, DaoException {
        //if (this.conn.isClosed() && this.conn.open()) {
        //    throw new DaoException("Connection to database cannot be setup.");
        //}
        Connection connection = this.conn.getConnection();
        if (connection == null) {
            throw new DaoException("Connection setup successfully, but we " +
                    "got a null connection instance.");
        }
        return connection.createStatement();
    }

    @Override
    public void addReply(ReplyInfo replyInfo) throws DaoException {
        try {
            Statement statement = this.createStatement();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String date = sdf.format(replyInfo.getDate());
            ResultSet rs = statement.executeQuery(String.format(
                    selectPidAndRid, replyInfo.getPid(), replyInfo.getRid()
            ));
            if (rs != null && rs.next()) {
                rs.close();
                statement.close();
                throw new RidHasExistException("Rid " + replyInfo.getRid() + " is existing.");
            } else if (rs == null) {
                statement.close();
                throw new DaoException("Unknown error occurs.");
            }
            if (statement.executeUpdate(String.format(
                    insert, replyInfo.getPid(), replyInfo.getRid(), replyInfo.getContent(),
                    replyInfo.getSignature(), date
            )) == 0) {
                rs.close();
                statement.close();
                throw new DaoException("Insertion into database failed.");
            }
            rs.close();
            statement.close();
        } catch (SQLException e) {
            throw new DaoException(
                    "This Exception is caused by SQLException: " + e.getCause() +
                            ", SQL State: " + e.getSQLState() +
                            ", Error Code: " + e.getErrorCode()
            );
        }
    }

    @Override
    public ReplyInfo getReply(long pid, long rid) throws DaoException {
        try {
            Statement statement = this.createStatement();
            ResultSet rs = statement.executeQuery(String.format(selectPidAndRid, pid, rid));
            if (rs == null) {
                statement.close();
                throw new DaoException("Unknown error occurs.");
            } else if (rs.next()) {
                ReplyInfo ret = new ReplyInfo(
                        rs.getLong("pid"),
                        rs.getLong("rid"),
                        rs.getString("content"),
                        rs.getString("signature"),
                        rs.getDate("date")
                );
                rs.close();
                statement.close();
                return ret;
            }
            rs.close();
            statement.close();
            throw new RidNotExistException("Rid " + rid + " is not existing.");
        } catch (SQLException e) {
            throw new DaoException(
                    "This Exception is caused by SQLException: " + e.getCause() +
                            ", SQL State: " + e.getSQLState() +
                            ", Error Code: " + e.getErrorCode()
            );
        }
    }

    @Override
    public void deleteReply(long pid, long rid) throws DaoException {
        try {
            Statement statement = this.createStatement();
            ResultSet rs = statement.executeQuery(String.format(selectPidAndRid, pid, rid));
            if (rs == null) {
                statement.close();
                throw new DaoException("Unknown error occurs.");
            } else if (rs.next()) {
                if (statement.executeUpdate(String.format(deletePidAndRid, pid, rid)) == 0) {
                    rs.close();
                    statement.close();
                    throw new DaoException("Deletion from database failed");
                }
                rs.close();
                statement.close();
            } else {
                rs.close();
                statement.close();
                throw new RidNotExistException("Rid " + rid + " is not existing.");
            }
        } catch (SQLException e) {
            throw new DaoException(
                    "This Exception is caused by SQLException: " + e.getCause() +
                            ", SQL State: " + e.getSQLState() +
                            ", Error Code: " + e.getErrorCode()
            );
        }
    }

    @Override
    public int getRepliesCount(long pid) throws DaoException {
        try {
            Statement statement = this.createStatement();
            ResultSet rs = statement.executeQuery(String.format(selectPid, pid));
            int count = 0;
            while (rs.next()) count++;
            rs.close();
            statement.close();
            return count;
        } catch (SQLException e) {
            throw new DaoException(
                    "This Exception is caused by SQLException: " + e.getCause() +
                            ", SQL State: " + e.getSQLState() +
                            ", Error Code: " + e.getErrorCode()
            );
        }
    }

    @Override
    public boolean contain(long pid, long rid) throws DaoException {
        try {
            Statement statement = this.createStatement();
            ResultSet rs = statement.executeQuery(String.format(selectPidAndRid, pid, rid));
            boolean ret = rs.next();
            rs.close();
            statement.close();
            return ret;
        } catch (SQLException e) {
            throw new DaoException(
                    "This Exception is caused by SQLException: " + e.getCause() +
                            ", SQL State: " + e.getSQLState() +
                            ", Error Code: " + e.getErrorCode()
            );
        }
    }

    @Override
    public long getLatestReplyRid(long pid) throws DaoException {
        try {
            Statement statement = this.createStatement();
            ResultSet rs = statement.executeQuery(String.format(
                    "SELECT * FROM reply WHERE pid=%d ORDER BY pid desc", pid
            ));
            if (rs != null) {
                if (rs.next()) {
                    long ret = rs.getLong("pid");
                    rs.close();
                    statement.close();
                    return ret;
                } else {
                    rs.close();
                    statement.close();
                    throw new DaoException("No corresponding reply.");
                }
            } else {
                statement.close();
                throw new DaoException("Unknown error occurs.");
            }
        } catch (SQLException e) {
            throw new DaoException(
                    "This Exception is caused by SQLException: " + e.getCause() +
                            ", SQL State: " + e.getSQLState() +
                            ", Error Code: " + e.getErrorCode()
            );
        }
    }

    @Override
    public long getEarliestReplyRid(long pid) throws DaoException {
        try {
            Statement statement = this.createStatement();
            ResultSet rs = statement.executeQuery(String.format(
                    "SELECT * FROM reply WHERE pid=%d ORDER BY pid", pid
            ));
            if (rs != null) {
                if (rs.next()) {
                    long ret = rs.getLong("pid");
                    rs.close();
                    statement.close();
                    return ret;
                } else {
                    rs.close();
                    statement.close();
                    throw new DaoException("No corresponding reply.");
                }
            } else {
                statement.close();
                throw new DaoException("Unknown error occurs.");
            }
        } catch (SQLException e) {
            throw new DaoException(
                    "This Exception is caused by SQLException: " + e.getCause() +
                            ", SQL State: " + e.getSQLState() +
                            ", Error Code: " + e.getErrorCode()
            );
        }
    }
}
